package sf.database.dialect.sqlserver;


import com.querydsl.sql.SQLServerTemplates;
import com.querydsl.sql.SQLTemplates;
import org.jooq.SQLDialect;
import sf.core.DBField;
import sf.database.dbinfo.Feature;
import sf.database.dbinfo.SequenceInfo;
import sf.database.dialect.DBDialect;
import sf.database.dialect.DBProperty;
import sf.database.dialect.DatabaseDriver;
import sf.database.jdbc.sql.Crud;
import sf.database.jdbc.type.TypeHandler;
import sf.database.meta.ColumnMapping;
import sf.database.meta.TableMapping;
import sf.database.support.RDBMS;
import sf.tools.JavaTypeUtils;
import sf.tools.StringUtils;

import java.sql.Connection;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class SqlServerDialect extends DBDialect {
    @Override
    public DatabaseDriver getDatabaseDriver() {
        return DatabaseDriver.SQLSERVER;
    }

    @Override
    public RDBMS getName() {
        return RDBMS.sqlserver;
    }

    // private static String COMMENT_TABLE =
    // "EXECUTE sp_updateextendedproperty N'Description', '$tableComment', N'user', N'dbo', N'table', N'$table', NULL, NULL";

    private static String COMMENT_COLUMN = "EXECUTE sp_addextendedproperty N'MS_Description', '%3$s', N'user', N'dbo', N'table', N'%1$s', N'column', N'%2$s'";


    public SqlServerDialect() {
        features.add(Feature.COLUMN_DEF_ALLOW_NULL);
        features.add(Feature.CONCAT_IS_ADD);
        features.add(Feature.NOT_SUPPORT_KEYWORD_DEFAULT);
        features.add(Feature.BATCH_GENERATED_KEY_BY_FUNCTION);
        features.add(Feature.SUPPORT_COMMENT);
        // features.add(Feature.NO_BIND_FOR_INSERT);
        // features.add(Feature.NO_BIND_FOR_SELECT);

        setProperty(DBProperty.ADD_COLUMN, "ADD");
        setProperty(DBProperty.MODIFY_COLUMN, "ALTER COLUMN");
        setProperty(DBProperty.DROP_COLUMN, "DROP COLUMN");
        setProperty(DBProperty.ADD_CONSTRAINT, "ADD CONSTRAINT");
        setProperty(DBProperty.CHECK_SQL, "select 1");
        setProperty(DBProperty.GET_IDENTITY_FUNCTION, "SELECT @@IDENTITY");
        setProperty(DBProperty.WRAP_FOR_KEYWORD, "[]");
        setProperty(DBProperty.DROP_INDEX_TABLE_PATTERN, "%2$s.%1$s");
        setProperty(DBProperty.NVL_FUNCTION,"isnull(%1$s,%2$s)");
    }

    @Override
    protected String unwrapper(String name) {
        return StringUtils.replaceEach(name, new String[]{"[", "]"}, new String[]{"", ""});
    }

    @Override
    public String getDefaultSchema() {
        return "dbo";
    }

    @Override
    public String getSqlStatmentSeparator() {
        return "go" + "\n";
    }

    @Override
    public String sqlTableDrop(String table) {
        String sql = "if exists (select * from dbo.sysobjects where id=object_id(N'%s') and objectproperty(id,N'IsUserTable')=1) drop table %s;";
        table = wrapKeyword(table);
        return String.format(sql, table, table);
    }

    @Override
    public String sqlTableRename(String oldName, String newName) {
        oldName = wrapKeyword(oldName);
        newName = wrapKeyword(newName);
        return "alter table  " + oldName + " rename to " + newName + ";";
    }

    @Override
    public String sqlColumnAdd(String table, String column_definition, String column_position) {
        return String.format("alter table %s add %s;", wrapKeyword(table), column_definition);
    }

    @Override
    public String sqlColumnModify(String table, String column_definition, String column_position) {
        return String.format("alter table %s alter column %s;", wrapKeyword(table), column_definition);
    }

    @Override
    public String sqlColumnDrop(String table, String column) {
        return String.format("alter table %s drop column %s;", wrapKeyword(table), wrapKeyword(column));
    }

    @Override
    public String sqlPageList(String sql, long offset, int limit) {
        StringBuilder sb = new StringBuilder();
        if (offset == 0) {
            sb.append("select top ").append(limit).append(" * from (").append(sql).append(") as temp");
        } else {
            throw new UnsupportedOperationException("not support,please use you own count sql and page sql!");
        }
        return sb.toString();
    }

    @Override
    public String getHibernateDialect() {
        return "org.hibernate.dialect.SQLServerDialect";
    }

    public boolean createEntity(Connection conn, TableMapping en) {
        StringBuilder sb = new StringBuilder("CREATE TABLE " + wrapKeyword(en.getTableName()) + "(");
        // 创建字段
        for (Map.Entry<DBField, ColumnMapping> entry : en.getSchemaMap().entrySet()) {
            ColumnMapping cm = entry.getValue();
            if (cm.getJpaTransient() != null)
                continue;
            sb.append('\n').append(wrapKeyword(cm.getRawColumnName()));
            sb.append(' ').append(evalFieldType(cm));
            // 自增主键特殊形式关键字
            if (cm.isPk() && cm.getGv() != null) {
                if (JavaTypeUtils.isNumberClass(cm.getClz())) {
                    sb.append(" IDENTITY(1,1) ");
                }
            }
            sb.append(',');
        }
        // 创建主键
        List<ColumnMapping> pks = en.getPkFields();
        if (!pks.isEmpty()) {
            sb.append('\n');
            sb.append("PRIMARY KEY (");
            boolean f = false;
            for (ColumnMapping pk : pks) {
                sb.append(f ? "," : "").append(wrapKeyword(pk.getRawColumnName()));
                f = true;
            }
            sb.append(") \n ");
        }
        // 结束表字段设置
        sb.setCharAt(sb.length() - 1, ')');

        List<String> sqls = new ArrayList<String>();
        sqls.add(sb.toString());

        // // 处理非主键unique
        // for (MappingField mf : en.getMappingFields()) {
        // if(!mf.isPk())
        // continue;
        // String sql =
        // gSQL("alter table ${T} add constraint unique_key_${F} unique (${F});",
        // en.getTableName(),mf.getColumnName());
        // sqls.add(Sqls.create(sql));
        // }
        // 处理AutoIncreasement

        // 创建索引
        List<String> list = createIndexSql(en);
        sqls.addAll(list);

        //创建约束
        list = createUniqueSql(en);
        sqls.addAll(list);

        // 执行创建语句
        for (String sql : sqls) {
            execute(sql, conn);
        }

        // 创建关联表
        createRelation(conn, en);

        // 添加注释(表注释与字段注释)
        addComment(conn, en);

        return true;
    }

    public String evalFieldType(ColumnMapping mf) {
        String type = "";
        Class<?> mirror = mf.getClz();
        if (mf.getColumn() == null || StringUtils.isBlank(getColumnDefinition(mf))) {
            String other = "";
            if (mf.getColumn() != null && mf.getColumn().unique()) {
                other = " UNIQUE ";
            }
            if (mf.getColumn() != null && !mf.getColumn().nullable()) {
                other = other + " NOT NULL ";
            }
            type = _evalFieldType(mf);
            type = type + other;
        }
        //覆盖columnDefinition
        if (StringUtils.isNotBlank(getColumnDefinition(mf))) {
            type = getColumnDefinition(mf);
        }
        //处理自定义映射
        if (mf.getType() != null) {
            Class<? extends TypeHandler> clz = mf.getType().value();
            try {
                TypeHandler handler = clz.newInstance();
                if (handler.getSqlType() == Types.VARCHAR) {
                    type = "TEXT";
                }
            } catch (InstantiationException | IllegalAccessException e) {
                throw new RuntimeException(e);
            }
        }
        // 其它的参照默认字段规则 ...
        return type;
    }

    private String _evalFieldType(ColumnMapping mf) {
        String type = "";
        switch (mf.getSqlType()) {
            case Types.CHAR: {
                int length = getColumnLength(mf);
                type = "CHAR(" + length + ")";
                break;
            }
            case Types.VARCHAR: {
                int length = getColumnLength(mf);
                type = "VARCHAR(" + length + ")";
                break;
            }
            case Types.TINYINT: {
                type = "TINYINT";
                break;
            }
            case Types.SMALLINT: {
                type = "SMALLINT";
                break;
            }
            case Types.INTEGER: {
                // 用户自定义了宽度
                int length = getColumnLength(mf);
                if (length > 0 && length <= 38) {
                    type = "NUMERIC(" + length + ")";
                } else {
                    // 用数据库的默认宽度
                    type = "INT";
                }
                break;
            }
            case Types.BIGINT: {
                type = "BIGINT";
                break;
            }
            case Types.FLOAT: {
                // 用户自定义了精度
                int scale = getColumnScale(mf);
                int precision = getColumnPrecision(mf);
                if (precision > 0 && scale > 0) {
                    type = "DECIMAL(" + precision + "," + scale + ")";
                } else {
                    type = "FLOAT";
                }
                break;
            }
            case Types.DOUBLE:
                // 用默认精度
                type = "DECIMAL(15,10)";
                break;
            case Types.BOOLEAN: {
                type = "BIT";
                break;
            }
            case Types.DECIMAL: {
                // BigDecimal
                int scale = getColumnScale(mf);
                int precision = getColumnPrecision(mf);
                if (scale > 0 && precision > 0) {
                    type = "DECIMAL(" + precision + "," + scale + ")";
                } else {
                    throw new UnsupportedOperationException("Unsupport type!");
                }
                break;
            }
            case Types.NUMERIC: {
                int scale = getColumnScale(mf);
                int precision = getColumnPrecision(mf);
                if (precision > 0) {
                    type = "NUMERIC(" + precision + "," + scale + ")";
                } else {
                    type = "NUMERIC(38,0)";
                }
                break;
            }
            case Types.DATE:
            case Types.TIME:
            case Types.TIMESTAMP:
                type = "DATETIME";
                break;
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                type = "VARBINARY(max)";
                break;
            case Types.CLOB:
            case Types.LONGVARCHAR:
            case Types.LONGNVARCHAR: {
                int length = getColumnLength(mf);
                if (mf.isLob() || (mf.getColumn() != null && length >= 1000)) {
                    type = "TEXT";
                }
                break;
            }
            default:
                type = super.evalFieldType(mf);
                break;
        }
        return type;
    }

    @Override
    public boolean dropEntity(Connection conn, TableMapping en) {
        return super.dropEntity(conn, en);
    }

    public void addComment(Connection con, TableMapping en) {
        // TODO 表注释 SQLServer2005中貌似不行
        // 字段注释
        if (!en.getMetaFields().isEmpty()) {
            List<String> sqls = new ArrayList<String>();
            for (ColumnMapping mf : en.getMetaFields()) {
                if (mf.getComment() != null && StringUtils.isNotBlank(mf.getComment().value())) {
                    //table,column,columnComment
                    //表名和字段名不能使用[]包括.
                    String s = String.format(COMMENT_COLUMN, unwrapper(en.getTableName()), unwrapper(mf.getRawColumnName()), mf.getComment().value());
                    sqls.add(s);
                }
            }

            // 执行创建语句
            for (String sql : sqls) {
                execute(sql, con);
            }
        }
    }

    @Override
    public String uniqueSql(String tableName, String name, String[] columnNames) {
        StringBuilder sb = new StringBuilder();
        sb.append("alter table ").append(wrapKeyword(tableName)).append(" add constraint ").append(name).append(" unique(");
        boolean f = false;
        for (int i = 0; i < columnNames.length; i++) {
            String column = columnNames[i];
            sb.append(f ? "," : "").append(wrapKeyword(column));
            f = true;
        }
        sb.append(")");
        return sb.toString();
    }

    @Override
    public List<SequenceInfo> getSequenceInfo(Connection conn, String schema, String seqName) {
        schema = StringUtils.isBlank(schema) ? "%" : schema;
        seqName = StringUtils.isBlank(seqName) ? "%" : seqName;
        String sql = "SELECT CONVERT(varchar,seq.name),CONVERT(int,seq.cache_size),"
                + "CONVERT(bigint,seq.current_value),"
                + "CONVERT(bigint,seq.minimum_value),"
                + "CONVERT(bigint,seq.start_value),"
                + "CONVERT(int,seq.increment),"
                + "CONVERT(varchar,m.name) as schema_name from sys.sequences seq, sys.schemas m WHERE seq.SCHEMA_ID=m.SCHEMA_ID "
                + "AND seq.name LIKE ? AND m.name LIKE ?";
        List<SequenceInfo> result = new ArrayList<>();
        Crud.getInstance().getCrudSql().selectResultSet(conn, (rs) -> {
            while (rs.next()) {
                SequenceInfo seq = new SequenceInfo();
                seq.setCatalog(null);
                seq.setSchema(rs.getString(1));
                seq.setName(rs.getString(2));
                seq.setMinValue(rs.getLong(3));
                //	seq.setMaxValue(rs.getLong(4));
                seq.setStep(rs.getInt(5));
                seq.setCacheSize(rs.getInt(6));
                seq.setCurrentValue(rs.getLong(7));
                result.add(seq);
            }
            return null;
        }, sql, schema, seqName);
        return result;
    }

    private static SQLTemplates sqlTemplates = SQLServerTemplates.DEFAULT;

    @Override
    public SQLTemplates getQueryDslDialect() {
        return sqlTemplates;
    }

    @Override
    public SQLDialect getJooqDialect() {
        //sqlserver 不支持
//        IdentifiersHelp.addOuterDB();
//        return SQLDialect.valueOf(IdentifiersHelp.SQLSERVER);

//        throw new UnsupportedOperationException("not support");
        return null;
    }
}
